--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_DEPB.HQL
--    Functions : 表38：存款余额分产品统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_DEPB PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4                   AS FIN_ORG_DIST
       ,XT.CCY                             AS CCY
       ,XT.DEPOSIT_TYPE                    AS DEPOSIT_TYPE -- 产品类别
       ,XT.TYPE_DESC                       AS TYPE_DESC    -- 产品类别描述
       ,SUM(XT.ACTUAL_BAL )/100000000      AS ACTUAL_BAL   -- 存款余额
       ,SUM(XT.BAL_LM     )/100000000      AS BAL_LM       -- 余额上期末
       ,SUM(XT.BAL_YF     )/100000000      AS BAL_YF       -- 余额年初
       ,SUM(XT.BAL_GROW   )/100000000      AS BAL_GROW     -- 同比增长
       ,SUM(XT.WSUM_BAL   )/100000000      AS WSUM_BAL     -- 余额加权值
       ,SUM(XT.WBAL_LM    )/100000000      AS WBAL_LM      -- 余额加权值上期末
       ,SUM(XT.WBAL_YF    )/100000000      AS WBAL_YF      -- 余额加权值年初
       ,SUM(XT.WBAL_LC    )/100000000      AS WBAL_LC      -- 余额加权值去年同期
FROM (
    -- 个人
    SELECT
         DEP.FIN_ORG_NO                                           AS FIN_ORG_NO
        ,DEP.CCY                                                  AS CCY
        ,'1'                                                      AS DEPOSIT_TYPE    -- 产品类别
        ,'个人'                                                   AS TYPE_DESC       -- 产品类别描述
        ,COALESCE(DEP.ACTUAL_BAL,0)                               AS ACTUAL_BAL      -- 存款余额
        ,COALESCE(DEP.BAL_LM,0)                                   AS BAL_LM          -- 余额上期末
        ,COALESCE(DEP.BAL_YF,0)                                   AS BAL_YF          -- 余额年初
        ,COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0)        AS BAL_GROW        -- 同比增长
        ,COALESCE(DEP.WSUM_BAL,0)                                 AS WSUM_BAL        -- 余额加权值
        ,COALESCE(DEP.WBAL_LM,0)                                  AS WBAL_LM         -- 余额加权值上期末
        ,COALESCE(DEP.WBAL_YF,0)                                  AS WBAL_YF         -- 余额加权值年初
        ,COALESCE(DEP.WBAL_LC,0)                                  AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069')AND CLIENT_TYPE = '1')DEP

    UNION ALL

    -- 个人_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款+协定存款+保证金存款
    SELECT
         DEP.FIN_ORG_NO                                                                                                     AS FIN_ORG_NO
        ,DEP.CCY                                                                                                            AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '11'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '12'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '13'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '14'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '15'
              WHEN DEP.DEPOSIT_TYPE IN ('D051','D052')  THEN '16'
              WHEN DEP.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '17'
         END                                                                                                                AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '活期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '定期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
              WHEN DEP.DEPOSIT_TYPE IN ('D051','D052')  THEN '协定存款'
              WHEN DEP.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '保证金存款'
         END                                                                                                                AS TYPE_DESC
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                         AS ACTUAL_BAL      -- 存款余额
        ,COALESCE(DEP.BAL_LM,0)                                                                                             AS BAL_LM          -- 余额上期末
        ,COALESCE(DEP.BAL_YF,0)                                                                                             AS BAL_YF          -- 余额年初
        ,COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0)                                                                  AS BAL_GROW        -- 同比增长
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                           AS WSUM_BAL        -- 余额加权值
        ,COALESCE(DEP.WBAL_LM,0)                                                                                            AS WBAL_LM         -- 余额加权值上期末
        ,COALESCE(DEP.WBAL_YF,0)                                                                                            AS WBAL_YF         -- 余额加权值年初
        ,COALESCE(DEP.WBAL_LC,0)                                                                                            AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '1')DEP

    UNION ALL

    -- 机构
    SELECT
         DEP.FIN_ORG_NO                                           AS FIN_ORG_NO
        ,DEP.CCY                                                  AS CCY
        ,'0'                                                      AS DEPOSIT_TYPE    -- 产品类别
        ,'机构'                                                   AS TYPE_DESC       -- 产品类别描述
        ,COALESCE(DEP.ACTUAL_BAL,0)                               AS ACTUAL_BAL      -- 存款余额
        ,COALESCE(DEP.BAL_LM,0)                                   AS BAL_LM          -- 余额上期末
        ,COALESCE(DEP.BAL_YF,0)                                   AS BAL_YF          -- 余额年初
        ,COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0)        AS BAL_GROW        -- 同比增长
        ,COALESCE(DEP.WSUM_BAL,0)                                 AS WSUM_BAL        -- 余额加权值
        ,COALESCE(DEP.WBAL_LM,0)                                  AS WBAL_LM         -- 余额加权值上期末
        ,COALESCE(DEP.WBAL_YF,0)                                  AS WBAL_YF         -- 余额加权值年初
        ,COALESCE(DEP.WBAL_LC,0)                                  AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '0')DEP

    UNION ALL

    -- 机构_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款+协定存款+保证金存款
    SELECT
         DEP.FIN_ORG_NO                                                                                                     AS FIN_ORG_NO
        ,DEP.CCY                                                                                                            AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '21'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '22'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '23'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '24'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '25'
              WHEN DEP.DEPOSIT_TYPE IN ('D051','D052')  THEN '26'
              WHEN DEP.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '27'
         END                                                                                                                AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '活期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '定期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
              WHEN DEP.DEPOSIT_TYPE IN ('D051','D052')  THEN '协定存款'
              WHEN DEP.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '保证金存款'
         END                                                                                                                AS TYPE_DESC
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                         AS ACTUAL_BAL      -- 存款余额
        ,COALESCE(DEP.BAL_LM,0)                                                                                             AS BAL_LM          -- 余额上期末
        ,COALESCE(DEP.BAL_YF,0)                                                                                             AS BAL_YF          -- 余额年初
        ,COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0)                                                                  AS BAL_GROW        -- 同比增长
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                           AS WSUM_BAL        -- 余额加权值
        ,COALESCE(DEP.WBAL_LM,0)                                                                                            AS WBAL_LM         -- 余额加权值上期末
        ,COALESCE(DEP.WBAL_YF,0)                                                                                            AS WBAL_YF         -- 余额加权值年初
        ,COALESCE(DEP.WBAL_LC,0)                                                                                            AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '0')DEP

    UNION ALL

    -- 合计 机构+个人
    SELECT
         DEP.FIN_ORG_NO                                           AS FIN_ORG_NO
        ,DEP.CCY                                                  AS CCY
        ,'HJ'                                                     AS DEPOSIT_TYPE    -- 产品类别
        ,'合计'                                                   AS TYPE_DESC       -- 产品类别描述
        ,COALESCE(DEP.ACTUAL_BAL,0)                               AS ACTUAL_BAL      -- 存款余额
        ,COALESCE(DEP.BAL_LM,0)                                   AS BAL_LM          -- 余额上期末
        ,COALESCE(DEP.BAL_YF,0)                                   AS BAL_YF          -- 余额年初
        ,COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0)        AS BAL_GROW        -- 同比增长
        ,COALESCE(DEP.WSUM_BAL,0)                                 AS WSUM_BAL        -- 余额加权值
        ,COALESCE(DEP.WBAL_LM,0)                                  AS WBAL_LM         -- 余额加权值上期末
        ,COALESCE(DEP.WBAL_YF,0)                                  AS WBAL_YF         -- 余额加权值年初
        ,COALESCE(DEP.WBAL_LC,0)                                  AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069'))DEP
    )XT
   -- 与地区表最细级关联
   LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON XT.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
   LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
   GROUP BY   AREA.AREA_CODE_4
             ,XT.CCY
             ,XT.DEPOSIT_TYPE
             ,XT.TYPE_DESC ;